package renthouse.dao;/*
 * @创建者：黄苟苟
 * @创建时间：2021/5/18   18:10
 * @Description描述
 **/





import renthouse.entry.House;

import renthouse.util.ConnDb;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;


public class HouseDao {
    
    public boolean insert(House house) {
        String s="insert into house(masterid,address,state,style,rent) values (?,?,?,?,?)";
        Connection connection= ConnDb.getConnection();
        PreparedStatement p=null;
        try {
            p=connection.prepareStatement(s);

            p.setInt(1,house.getMasterid());
            p.setString(2,house.getAddress());
            p.setString(3,house.getState());
            p.setString(4,house.getStyle());
            p.setInt(5,house.getRent());


            int i=  p.executeUpdate();
            if (i>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,p,null);  //关闭
        }
        return false;
    }
    public House queryById(int houseid) {
        House house=new House();
        String query="SELECT * FROM house where houseid="+houseid;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;
        int len=0;
        try {
            statement=connection.createStatement();
            resultSet=statement.executeQuery(query);
            while (resultSet.next()){
                house.setHouseid(resultSet.getInt("houseid"));
                house.setMasterid(resultSet.getInt("masterid"));
                house.setState(resultSet.getString("state"));
                house.setStyle(resultSet.getString("style"));
                house.setAddress(resultSet.getString("address"));
                house.setRent(resultSet.getInt("rent"));

            }

        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            ConnDb.close(connection,statement,null);
        }

        return house;
    }

   
    public boolean deleteById(int i) {
        String deletestr="delete from house where houseid="+i;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(deletestr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;
    }

   
    public boolean updateById(House house) {
        if (house.getHouseid()==0){
            return false;
        }
        String updateStr="update house set houseid="+house.getHouseid();

        if (house.getMasterid()!=0){
            updateStr+=",masterid="+house.getMasterid();
        }
        
        if (house.getAddress()!=null){
            updateStr+=",address='"+house.getAddress()+"'";
        }

        if (house.getState()!=null){
            updateStr+=",state='"+house.getState()+"'";
        }

        if (house.getStyle()!=null){
            updateStr+=",style='"+house.getStyle()+"'";
        }
        if (house.getRent()!=0){
            updateStr+=",rent="+house.getRent();
        }

        //加上条件
        updateStr+=" where houseid="+house.getHouseid();


        Connection connection= ConnDb.getConnection();
        Statement statement= ConnDb.getStatement(connection);
        try {
            int len=statement.executeUpdate(updateStr);
            if (len>0){
                return true;
            }
        } catch (SQLException e) {
            e.printStackTrace();
            return false;
        }finally {
            ConnDb.close(connection,statement,null);
        }
        return false;

    }

    
    public List <House> query(House house) {
        String str="SELECT * FROM house where 1=1 ";
        List<House> list=new ArrayList <>();

        if (house.getMasterid()!=0){
            str+=" and masterid ="+house.getMasterid();
        }

        if (house.getAddress()!=null){
            str+=" and address like concat('%','"+house.getAddress()+"','%')";
        }

        if (house.getState()!=null){
            str+=" and state like concat('%','"+house.getState()+"','%')";
        }

        if (house.getStyle()!=null){
            str+=" and style like concat('%','"+house.getStyle()+"','%')";
        }
        if (house.getRent()!=0){
            str+="and rent="+house.getRent();
        }


        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                House House1=new House();
                House1.setHouseid(resultSet.getInt("houseid"));
                House1.setMasterid(resultSet.getInt("masterid"));
                House1.setAddress(resultSet.getString("address"));
                House1.setState(resultSet.getString("state"));
                House1.setStyle(resultSet.getString("style"));
                House1.setRent(resultSet.getInt("rent"));

                list.add(House1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    
    public List <House> queryForPage(House house, int pageNum, int pageSize) {
        int start = (pageNum - 1) * pageSize;
        String str="SELECT * FROM house where 1=1 ";
        List<House> list=new ArrayList <>();

        if (house.getMasterid()!=0){
            str+=" and masterid ="+house.getMasterid();
        }

        if (house.getAddress()!=null){
            str+=" and address like concat('%','"+house.getAddress()+"','%')";
        }

        if (house.getState()!=null){
            str+=" and state like concat('%','"+house.getState()+"','%')";
        }

        if (house.getStyle()!=null){
            str+=" and style like concat('%','"+house.getStyle()+"','%')";
        }
        if (house.getRent()!=0){
            str+="and rent="+house.getRent();
        }

        str += " limit " + start + "," + pageSize;
        Connection connection=ConnDb.getConnection();
        Statement statement=ConnDb.getStatement(connection);
        ResultSet resultSet=null;

        try {
            resultSet=statement.executeQuery(str);
            while (resultSet.next()){
                House House1=new House();
                House1.setHouseid(resultSet.getInt("houseid"));
                House1.setMasterid(resultSet.getInt("masterid"));
                House1.setAddress(resultSet.getString("address"));
                House1.setState(resultSet.getString("state"));
                House1.setStyle(resultSet.getString("style"));
                House1.setRent(resultSet.getInt("rent"));

                list.add(House1);
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }
        return list;
    }

    public int queryCount(House house) {


        String str = "SELECT count(1) countNum FROM house where 1=1 ";
        if (house.getMasterid()!=0){
            str+=" and masterid ="+house.getMasterid();
        }
        if (house.getAddress()!=null){
            str+=" and address like concat('%','"+house.getAddress()+"','%')";
        }

        if (house.getState()!=null){
            str+=" and state like concat('%','"+house.getState()+"','%')";
        }

        if (house.getStyle()!=null){
            str+=" and style like concat('%','"+house.getStyle()+"','%')";
        }
        if (house.getRent()!=0){
            str+="and rent="+house.getRent();
        }


        Connection connection = ConnDb.getConnection();
        Statement statement = ConnDb.getStatement(connection);
        ResultSet resultSet = null;

        int count=0;
        try {
            resultSet = statement.executeQuery(str);
            while (resultSet.next()) {
                count=resultSet.getInt("countNum");
            }

        } catch (SQLException e) {
            e.printStackTrace();
        }


        return count;
    }
}
